package com.cz.fra.utils;

import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;


public class DBUtil {

	private Connection connection = null;
	private Statement stmt = null;
	private ResultSet rs = null; 
	
	private static String driverName;
	
	private static String url;
	
	private static String username;
	
	private static String password;
	
	public static String databaseName;
	/**
	 * Constructor
	 */
	public DBUtil(){
		readJDBCProperties();
	}
	
	private void readJDBCProperties(){
		InputStream in = this.getClass().getClassLoader().getResourceAsStream("/res/jdbc.properties");
		Properties pro = new Properties();  
        try {
			pro.load(in);
		} catch (IOException e) {
			e.printStackTrace();
		} 
        driverName = pro.getProperty("jdbc.driverClassName");
		url = pro.getProperty("jdbc.url");  
		databaseName = pro.getProperty("jdbc.database");  
        username = pro.getProperty("jdbc.username");  
        password = pro.getProperty("jdbc.password");  
	}
	
	public Connection getConnection(){
		try{
			Class.forName(driverName);
			String _url = url + databaseName + "?autoReconnect=true";
			connection = DriverManager.getConnection(_url,username,password);
			stmt = connection.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_READ_ONLY);
		}
		catch(Exception e){
			e.printStackTrace();
		}
		return connection;
	}
	
	public void closeConnection(){
		try {
			if(stmt != null){
				stmt.close();
			}
			if(connection != null){
				connection.close();
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	/**
	 * 
	 * @param sql
	 * @return
	 */
	@SuppressWarnings("finally")
	public boolean executeUpdate(String sql){
		boolean success = false;
		try{
			success = stmt.executeUpdate(sql) > 0 ? true: false;
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			return success; 
		}
	}
	
	/**
	 * 
	 * @param sql
	 * @return
	 * @throws SQLException
	 */
	public ResultSet executeQuery(String sql) throws SQLException{
		try{
			rs = stmt.executeQuery(sql);
			return rs;
		}catch(Exception e){
			e.printStackTrace();
			return null;
		}
	}
}
